最近在公司剛好接觸到 Postgresql 的資料回收機制，今天就來聊聊～

VACCUM

garbage-collect and optionally analyze a database
資源回收並且選擇性地重整資料庫

在 PostgreSQL 的操作中，其實被刪除或更新的儲存空間，並完全在磁碟中被釋放，如果沒有執行回收的指令，當你查看他的儲存空間時，只會發現他一直增大。直到執行 VACUUM。

實驗

• 這是我們前兩天使用的 DB 我大概在 裡面塞了幾十萬筆資料。
• 我們先看一下他目前佔的空間

ithome=# select
  table_name,
  pg_size_pretty(pg_total_relation_size(quote_ident(table_name)))
from information_schema.tables
where table_schema = 'public'
order by 2 desc;
 table_name | pg_size_pretty
------------+----------------
 users      | 78 MB
 orders     | 16 kB

接著我們刪除 users 的所有資料

ithome=# delete from users ;
DELETE 1000000

你會發現，咦，我整張 table 都刪掉了，怎麼都沒變呢？

ithome=# select
  table_name,
  pg_size_pretty(pg_total_relation_size(quote_ident(table_name)))
from information_schema.tables
where table_schema = 'public'
order by 2 desc;
 table_name | pg_size_pretty
------------+----------------
 users      | 78 MB
 orders     | 16 kB

這時候我們使用 VACUUM FULL;

VACUUM FULL;

結束後我們才能看到他儲存空間恢復到正常的數字了。
這就是 Postgres 的資料回收機制。

ithome=# select
  table_name,
  pg_size_pretty(pg_total_relation_size(quote_ident(table_name)))
from information_schema.tables
where table_schema = 'public'
order by 2 desc;
 table_name | pg_size_pretty
------------+----------------
 users      | 16 kB
 orders     | 16 kB

VACUUM

在 Postgres的文件中給出的建議是，至少一天要在離峰時間執行一次 VACUUM 和 ANALYZE

VACUUM 有好幾個參數可以做使用

VACUUM;
VACUUM FULL;
VACUUM ANALYZE;
VACUUM table_name;

VACUUM;
VACUUM table_name;

普通的 VACUUM（不帶 FULL）只會回收空間並讓那些空間可以被使用，並不會清空磁碟。
也就是如果你在刪除資料後，執行 VACUUM，再查看一次空間時，你會發現它不是完全清除的。

透過以下指令可以查看目前的 dead_rows 和 live_rows

ithome=# SELECT
    schemaname,
    relname,
    n_tup_ins AS rows_inserted,
    n_tup_upd AS rows_updated,
    n_tup_del AS rows_deleted,
    n_live_tup AS live_rows,
    n_dead_tup AS dead_rows
FROM
    pg_stat_user_tables;
 schemaname | relname | rows_inserted | rows_updated | rows_deleted | live_rows | dead_rows
------------+---------+---------------+--------------+--------------+-----------+-----------
 public     | users   |       2400010 |            0 |      2500014 |   1000000 |         0
 public     | orders  |        100008 |            0 |       100008 |         0 |         0

table_name | pg_size_pretty
------------+----------------
 users      | 78 MB
 orders     | 16 kB

VACUUM;

執行完後空間下降到 28MB。
dead_rows 變成了 1000000。

ithome=# select
  table_name,
  pg_size_pretty(pg_total_relation_size(quote_ident(table_name)))
from information_schema.tables
where table_schema = 'public'
order by 2 desc;
 table_name | pg_size_pretty
------------+----------------
 users      | 28 MB
 orders     | 16 kB

ithome=# SELECT
    schemaname,
    relname,
    n_tup_ins AS rows_inserted,
    n_tup_upd AS rows_updated,
    n_tup_del AS rows_deleted,
    n_live_tup AS live_rows,
    n_dead_tup AS dead_rows
FROM
    pg_stat_user_tables;
 schemaname | relname | rows_inserted | rows_updated | rows_deleted | live_rows | dead_rows
------------+---------+---------------+--------------+--------------+-----------+-----------
 public     | users   |       3400010 |            0 |      4500014 |         0 |   1000000
 public     | orders  |        100008 |            0 |       100008 |         0 |         0

這時候執行 VACUUM 後
空間沒變，但 dead_rows 歸零了，代表那些空間被釋放出來可以給其他操作做使用，但不會釋放磁碟空間。

ithome=# select
  table_name,
  pg_size_pretty(pg_total_relation_size(quote_ident(table_name)))
from information_schema.tables
where table_schema = 'public'
order by 2 desc;
 table_name | pg_size_pretty
------------+----------------
 users      | 28 MB
 orders     | 16 kB

ithome=# SELECT
    schemaname,
    relname,
    n_tup_ins AS rows_inserted,
    n_tup_upd AS rows_updated,
    n_tup_del AS rows_deleted,
    n_live_tup AS live_rows,
    n_dead_tup AS dead_rows
FROM
    pg_stat_user_tables;
 schemaname | relname | rows_inserted | rows_updated | rows_deleted | live_rows | dead_rows
------------+---------+---------------+--------------+--------------+-----------+-----------
 public     | users   |       3400010 |            0 |      4500014 |         0 |         0
 public     | orders  |        100008 |            0 |       100008 |         0 |         0

VACUUM FULL;
如我們剛剛的例子，VACUUM FULL;會完全清除資料，並恢復磁碟的空間。
你可能會想，那為什麼不執行 VACUUM FULL就好，這是因為當執行 VACUUM FULL時，會 lock 住table ，所以若是 prod 的資料庫一直會被使用，就不適合使用 VACUUM FULL;

ANALYZE
通常在執行 VACUUM 前，都會先執行 ANALYZE，因為 ANALYZE 會先把目前表的統計資訊更新到最新，包括 live_rows 和 dead_rows

VACUUM ANALYZE
就是兩者的結合，同時做使用。

AUTOVACUUM

你可能會想，VACUUM 那麼重要，但如果都要手動執行，會很浪費時間，在 Postgresql 有 default 的設定是會自動執行 VACUUM 的，那就是 AUTOVACUUM。
AUTOVACUUM 中包含了 ANALYZE 和 VACUUM

以下有幾個常用的參數：

• **autovacuum_vacuum_scale_factor**: 0.01 (default: 0.2) 為當 table 中多少資料被修改後，會自動執行 VACUUM。
• **autovacuum_vacuum_threshold**: 50 (default: 50) 會和 autovacuum_vacuum_scale_factor，一起做使用，是最低的基準點。
• **autovacuum_naptime**: 1min (default: 1 min) 為自動清理的間隔時間。
• **總列數 * autovacuum_vacuum_scale_factor)+autovacuum_vacuum_threshold→ autovacuum**
  • 假設一張表的 rows 有 100000
  • 100000*0.2 +50 = 20050
  • 也就是當 20050 列被操作後，就會自動執行 VACUUM

如果你已經設定好 AUTO VACUUM 你可以用以下指令查看過去的紀錄。

SELECT
  relname,
  n_dead_tup AS dead_tuples,
  last_autovacuum,
  last_vacuum,
  last_analyze,
  last_autoanalyze
FROM
  pg_stat_user_tables
WHERE
  relname = 'FastableImages';

你可以在 postgresql 中直接修改，也可以透過指令來修改

ALTER TABLE tab SET (autovacuum_vacuum_scale_factor = 0.01);
alter table "FastableImages" set (autovacuum_vacuum_scale_factor=0.01, autovacuum_vacuum_threshold=10000)

Reference

• https://docs.postgresql.tw/reference/sql-commands/vacuum